﻿using Dapper;
using MySql.Data.MySqlClient;
using NPOI.SS.Formula.Functions;
using Org.BouncyCastle.Crypto;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing.Drawing2D;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using System.Xml;
using XiAnAirport.BUS;
using XiAnAirport.Models;

namespace XiAnAirport.DAL
{
    public class ElevatorAreaDAL
    {
        public string AddElevator(Elevator elevatorinfo)
        {
            string strSql = "INSERT INTO elevator(elevatorNo,elevatorName,groupId,floorInf,dummy_floor,isDelete,elevatorType,speed,`load`,opcNO,outofservice,contractNo,saleNo,ipAddress,throughDoor,manufacturer,model,physicalNumber,certificateNumber,regNumber,isTemperature,videoPlayTime,temperatureThreshold,advertisement,captions,bendThreshold,cameraNo,areaId,outofserviceTime,ZLX,ZLY)VALUES(@elevatorNo,@elevatorName,@groupId,@floorInf,@dummy_floor,0,@elevatorType,@speed,@load,@opcNO,@outofservice,@contractNo,@saleNo,@ipAddress,@throughDoor,@manufacturer,@model,@physicalNumber,@certificateNumber,@regNumber,@isTemperature,@videoPlayTime,@temperatureThreshold,@advertisement,@captions,@bendThreshold,@cameraNo,@areaId,@outofserviceTime,@ZLX,@ZLY)";
            try
            {
                int count = 0;
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);
                    mySqlCommand.Parameters.AddWithValue("@elevatorNo", elevatorinfo.elevatorNO);
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", elevatorinfo.elevatorName);
                    mySqlCommand.Parameters.AddWithValue("@groupId", elevatorinfo.groupId);
                    mySqlCommand.Parameters.AddWithValue("@floorInf", elevatorinfo.floorInf.Replace('，', ','));
                    mySqlCommand.Parameters.AddWithValue("@dummy_floor", elevatorinfo.dummy_floor);
                    mySqlCommand.Parameters.AddWithValue("@elevatorType", elevatorinfo.elevatorType);
                    mySqlCommand.Parameters.AddWithValue("@speed", elevatorinfo.speed);
                    mySqlCommand.Parameters.AddWithValue("@load", elevatorinfo.load);
                    mySqlCommand.Parameters.AddWithValue("@opcNO", elevatorinfo.opcNO);
                    mySqlCommand.Parameters.AddWithValue("@outofservice", elevatorinfo.outofservice);
                    mySqlCommand.Parameters.AddWithValue("@contractNo", elevatorinfo.contractNo);
                    mySqlCommand.Parameters.AddWithValue("@saleNo", elevatorinfo.saleNo);
                    if (string.IsNullOrEmpty(elevatorinfo.outofserviceTime))
                    {
                        mySqlCommand.Parameters.AddWithValue("@outofserviceTime", DBNull.Value);
                    }
                    else
                    {
                        mySqlCommand.Parameters.AddWithValue("@outofserviceTime", Convert.ToDateTime(elevatorinfo.outofserviceTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    mySqlCommand.Parameters.AddWithValue("@ipAddress", elevatorinfo.ipAddress);
                    mySqlCommand.Parameters.AddWithValue("@throughDoor", elevatorinfo.throughDoor);
                    mySqlCommand.Parameters.AddWithValue("@manufacturer", elevatorinfo.manufacturer);
                    mySqlCommand.Parameters.AddWithValue("@model", elevatorinfo.model);
                    mySqlCommand.Parameters.AddWithValue("@physicalNumber", elevatorinfo.physicalNumber);
                    mySqlCommand.Parameters.AddWithValue("@certificateNumber", elevatorinfo.certificateNumber);
                    mySqlCommand.Parameters.AddWithValue("@regNumber", elevatorinfo.regNumber);
                    mySqlCommand.Parameters.AddWithValue("@isTemperature", elevatorinfo.isTemperature);
                    mySqlCommand.Parameters.AddWithValue("@videoPlayTime", elevatorinfo.videoPlayTime);
                    mySqlCommand.Parameters.AddWithValue("@temperatureThreshold", elevatorinfo.temperatureThreshold);
                    mySqlCommand.Parameters.AddWithValue("@advertisement", elevatorinfo.advertisement);
                    mySqlCommand.Parameters.AddWithValue("@captions", elevatorinfo.captions);
                    mySqlCommand.Parameters.AddWithValue("@bendThreshold", elevatorinfo.bendThreshold);
                    mySqlCommand.Parameters.AddWithValue("@cameraNo", elevatorinfo.cameraNo);
                    mySqlCommand.Parameters.AddWithValue("@areaId", elevatorinfo.areaId);
                    mySqlCommand.Parameters.AddWithValue("@ZLX", elevatorinfo.ZLX);
                    mySqlCommand.Parameters.AddWithValue("@ZLY", elevatorinfo.ZLY);

                    connection.Open();
                    count = mySqlCommand.ExecuteNonQuery();
                }

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        //public string AddElevator(Elevator elevatorinfo)
        //{
        //    string strSql = string.Format("INSERT INTO elevator(elevatorNo,elevatorName,groupId,floorInf,dummy_floor,isDelete,elevatorType,speed,`load`,opcNO,outofservice,contractNo,saleNo,outofserviceTime,ipAddress,throughDoor,manufacturer,model,physicalNumber,certificateNumber,regNumber,isTemperature,videoPlayTime,temperatureThreshold,advertisement,captions,bendThreshold,cameraNo,areaId)VALUES('{0}','{1}',{2},'{3}','{4}',0,'{5}',{6},{7},'{8}',{9},'{10}','{11}',{12},'{13}',{14},'{15}','{16}','{17}','{18}','{19}',{20},{21},{22},'{23}','{24}',{25},'{26}',{27})"
        //        , elevatorinfo.elevatorNO, elevatorinfo.elevatorName, elevatorinfo.groupId, elevatorinfo.floorInf.Replace('，', ','), elevatorinfo.dummy_floor, elevatorinfo.elevatorType, elevatorinfo.speed, elevatorinfo.load, elevatorinfo.opcNO, elevatorinfo.outofservice, elevatorinfo.contractNo, elevatorinfo.saleNo, elevatorinfo.outofserviceTime, elevatorinfo.ipAddress, elevatorinfo.throughDoor, elevatorinfo.manufacturer, elevatorinfo.model, elevatorinfo.physicalNumber, elevatorinfo.certificateNumber, elevatorinfo.regNumber, elevatorinfo.isTemperature, elevatorinfo.videoPlayTime, elevatorinfo.temperatureThreshold, elevatorinfo.advertisement, elevatorinfo.captions, elevatorinfo.bendThreshold, elevatorinfo.cameraNo, elevatorinfo.areaId);
        //    try
        //    {
        //        int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);


        //        if (count > 0)
        //        {
        //            return "success";
        //        }
        //        return "fail";
        //    }
        //    catch (Exception e)
        //    {
        //        return "fail," + e.ToString();
        //    }
        //}

        public string EditElevator(Elevator elevatorinfo)
        {
            string strSql = "UPDATE elevator SET elevatorNo=@elevatorNo,elevatorName=@elevatorName,groupId=@groupId,floorInf=@floorInf,dummy_floor=@dummy_floor,elevatorType=@elevatorType,speed=@speed,`load`=@load,opcNO=@opcNO,outofservice=@outofservice,contractNo=@contractNo,saleNo=@saleNo,outofserviceTime=@outofserviceTime,ipAddress=@ipAddress,throughDoor=@throughDoor,manufacturer=@manufacturer,model=@model,physicalNumber=@physicalNumber,certificateNumber=@certificateNumber,regNumber=@regNumber,isTemperature=@isTemperature,videoPlayTime=@videoPlayTime,temperatureThreshold=@temperatureThreshold,advertisement=@advertisement,captions=@captions,bendThreshold=@bendThreshold,cameraNo=@cameraNo,areaId=@areaId,ZLX=@ZLX,ZLY=@ZLY WHERE id=@id";
            try
            {
                int count = 0;
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);
                    mySqlCommand.Parameters.AddWithValue("@elevatorNo", elevatorinfo.elevatorNO);
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", elevatorinfo.elevatorName);
                    mySqlCommand.Parameters.AddWithValue("@groupId", elevatorinfo.groupId);
                    mySqlCommand.Parameters.AddWithValue("@floorInf", elevatorinfo.floorInf.Replace('，', ','));
                    mySqlCommand.Parameters.AddWithValue("@dummy_floor", elevatorinfo.dummy_floor);
                    mySqlCommand.Parameters.AddWithValue("@elevatorType", elevatorinfo.elevatorType);
                    mySqlCommand.Parameters.AddWithValue("@speed", elevatorinfo.speed);
                    mySqlCommand.Parameters.AddWithValue("@load", elevatorinfo.load);
                    mySqlCommand.Parameters.AddWithValue("@opcNO", elevatorinfo.opcNO);
                    mySqlCommand.Parameters.AddWithValue("@outofservice", elevatorinfo.outofservice);
                    mySqlCommand.Parameters.AddWithValue("@contractNo", elevatorinfo.contractNo);
                    mySqlCommand.Parameters.AddWithValue("@saleNo", elevatorinfo.saleNo);
                    if (string.IsNullOrEmpty(elevatorinfo.outofserviceTime))
                    {
                        mySqlCommand.Parameters.AddWithValue("@outofserviceTime", DBNull.Value);
                    }
                    else
                    {
                        mySqlCommand.Parameters.AddWithValue("@outofserviceTime", Convert.ToDateTime(elevatorinfo.outofserviceTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    mySqlCommand.Parameters.AddWithValue("@ipAddress", elevatorinfo.ipAddress);
                    mySqlCommand.Parameters.AddWithValue("@throughDoor", elevatorinfo.throughDoor);
                    mySqlCommand.Parameters.AddWithValue("@manufacturer", elevatorinfo.manufacturer);
                    mySqlCommand.Parameters.AddWithValue("@model", elevatorinfo.model);
                    mySqlCommand.Parameters.AddWithValue("@physicalNumber", elevatorinfo.physicalNumber);
                    mySqlCommand.Parameters.AddWithValue("@certificateNumber", elevatorinfo.certificateNumber);
                    mySqlCommand.Parameters.AddWithValue("@regNumber", elevatorinfo.regNumber);
                    mySqlCommand.Parameters.AddWithValue("@isTemperature", elevatorinfo.isTemperature);
                    mySqlCommand.Parameters.AddWithValue("@videoPlayTime", elevatorinfo.videoPlayTime);
                    mySqlCommand.Parameters.AddWithValue("@temperatureThreshold", elevatorinfo.temperatureThreshold);
                    mySqlCommand.Parameters.AddWithValue("@advertisement", elevatorinfo.advertisement);
                    mySqlCommand.Parameters.AddWithValue("@captions", elevatorinfo.captions);
                    mySqlCommand.Parameters.AddWithValue("@bendThreshold", elevatorinfo.bendThreshold);
                    mySqlCommand.Parameters.AddWithValue("@cameraNo", elevatorinfo.cameraNo);
                    mySqlCommand.Parameters.AddWithValue("@areaId", elevatorinfo.areaId);
                    mySqlCommand.Parameters.AddWithValue("@id", elevatorinfo.id);
                    mySqlCommand.Parameters.AddWithValue("@ZLX", elevatorinfo.ZLX);
                    mySqlCommand.Parameters.AddWithValue("@ZLY", elevatorinfo.ZLY);

                    connection.Open();
                    count = mySqlCommand.ExecuteNonQuery();
                }

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        //public string EditElevator(Elevator elevatorinfo)
        //{
        //    string strSql = string.Format("UPDATE elevator SET elevatorNo='{0}',elevatorName='{1}',groupId={2},floorInf='{3}',dummy_floor='{4}',elevatorType='{5}',speed={6},`load`={7},opcNO='{8}',outofservice={9},contractNo='{10}',saleNo='{11}',outofserviceTime={12},ipAddress='{13}',throughDoor='{14}',manufacturer='{15}',model='{16}',physicalNumber='{17}',certificateNumber='{18}',regNumber='{19}',isTemperature={20},videoPlayTime={22},temperatureThreshold={23},advertisement='{24}',captions='{25}',bendThreshold={26},cameraNo='{27}',areaId={28} WHERE id={21}"
        //        , elevatorinfo.elevatorNO, elevatorinfo.elevatorName, elevatorinfo.groupId, elevatorinfo.floorInf.Replace('，', ','), elevatorinfo.dummy_floor, elevatorinfo.elevatorType, elevatorinfo.speed, elevatorinfo.load, elevatorinfo.opcNO, elevatorinfo.outofservice, elevatorinfo.contractNo, elevatorinfo.saleNo, elevatorinfo.outofserviceTime, elevatorinfo.ipAddress, elevatorinfo.throughDoor, elevatorinfo.manufacturer, elevatorinfo.model, elevatorinfo.physicalNumber, elevatorinfo.certificateNumber, elevatorinfo.regNumber, elevatorinfo.isTemperature, elevatorinfo.id, elevatorinfo.videoPlayTime, elevatorinfo.temperatureThreshold, elevatorinfo.advertisement, elevatorinfo.captions, elevatorinfo.bendThreshold, elevatorinfo.cameraNo, elevatorinfo.areaId);
        //    try
        //    {
        //        if (SqlHelper<UserInfo>.SqlExecute(strSql))
        //        {
        //            return "success";
        //        }
        //        else
        //        {
        //            return "fail";
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        return "fail," + e.ToString();
        //    }
        //}

        public string DelElevator(int elevatorid)
        {
            string strSql = "delete FROM  elevator WHERE id=@id";
            try
            {
                int count = 0;
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    mySqlCommand.Parameters.AddWithValue("@id", elevatorid);

                    connection.Open();
                    count = mySqlCommand.ExecuteNonQuery();
                }

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        //public string DelElevator(int elevatorid)
        //{
        //    string strSql = string.Format("delete FROM  elevator WHERE id={0}", elevatorid);
        //    try
        //    {
        //        int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

        //        if (count > 0)
        //        {
        //            return "success";
        //        }
        //        return "fail";
        //    }
        //    catch (Exception e)
        //    {
        //        return "fail," + e.ToString();
        //    }
        //}

        public string BatchDelElevator(string ids)
        {
            string strSql = "delete FROM elevator WHERE id in (@ids)";
            try
            {
                int count = 0;
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);
                    mySqlCommand.Parameters.AddWithValue("@ids", ids);

                    connection.Open();
                    count = mySqlCommand.ExecuteNonQuery();
                }

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        //public string BatchDelElevator(string ids)
        //{
        //    string strSql = string.Format("delete FROM elevator WHERE id in ({0})", ids);
        //    try
        //    {
        //        int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

        //        if (count > 0)
        //        {
        //            return "success";
        //        }
        //        return "fail";
        //    }
        //    catch (Exception e)
        //    {
        //        return "fail," + e.ToString();
        //    }
        //}

        public string DelElevatorPosition(int elevatorId)
        {
            string strSql = "UPDATE elevator_position SET isDelete =1 WHERE elevatorId=@elevatorId";

            try
            {
                int count = 0;
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);
                    mySqlCommand.Parameters.AddWithValue("@elevatorId", elevatorId);

                    connection.Open();
                    count = mySqlCommand.ExecuteNonQuery();
                }

                if (count >= 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        //public string DelElevatorPosition(int elevatorId)
        //{
        //    string strSql = string.Format("UPDATE elevator_position SET isDelete =1 WHERE elevatorId={0}", elevatorId);

        //    try
        //    {
        //        int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

        //        if (count >= 0)
        //        {
        //            return "success";
        //        }
        //        return "fail";
        //    }
        //    catch (Exception e)
        //    {
        //        return "fail," + e.ToString();
        //    }
        //}

        public string BatchDelElevatorPosition(string ids)
        {
            string strSql = "UPDATE elevator_position SET isDelete =1 WHERE elevatorId in (@ids)";

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<Elevator> QueryElevator(Elevator elevatorinfo)
        {
            try
            {
                List<Elevator> list = new List<Elevator>();
                string strSql = "SELECT vi_elevatorinfo.*,area.areaName AS mainAreaName FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id";
                string sqlwhere = string.Empty;
                if (!string.IsNullOrEmpty(elevatorinfo.elevatorNO))
                {
                    sqlwhere += " vi_elevatorinfo.elevatorNO like @elevatorNO AND";
                }
                if (!string.IsNullOrEmpty(elevatorinfo.elevatorName))
                {
                    sqlwhere += " vi_elevatorinfo.elevatorName like @elevatorName AND";
                }
                if (!string.IsNullOrEmpty(elevatorinfo.areaName))
                {
                    sqlwhere += " vi_elevatorinfo.areaName like @areaName AND";
                }
                if (elevatorinfo.areaId > 0)
                {
                    sqlwhere += " vi_elevatorinfo.areaId=@areaId AND";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4); ;
                }
                strSql += " ORDER BY vi_elevatorinfo.id asc";

                if (elevatorinfo.Recordhead > -1 && elevatorinfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likeno = "%" + elevatorinfo.elevatorNO + "%";
                    string likename = "%" + elevatorinfo.elevatorName + "%";
                    string likearea = "%" + elevatorinfo.areaName + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorNO", $"%{likeno}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@areaName", $"%{likearea}%");
                    mySqlCommand.Parameters.AddWithValue("@areaId", elevatorinfo.areaId);
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", elevatorinfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", elevatorinfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<Elevator>(dataTable);
                }

                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public int QueryElevatorConut(Elevator elevatorinfo)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id";
                string sqlwhere = string.Empty;
                if (!string.IsNullOrEmpty(elevatorinfo.elevatorNO))
                {
                    sqlwhere += " vi_elevatorinfo.elevatorNO like @elevatorNO AND";
                }
                if (!string.IsNullOrEmpty(elevatorinfo.elevatorName))
                {
                    sqlwhere += " vi_elevatorinfo.elevatorName like @elevatorName AND";
                }
                if (!string.IsNullOrEmpty(elevatorinfo.areaName))
                {
                    sqlwhere += " vi_elevatorinfo.areaName like @areaName AND";
                }
                if (elevatorinfo.areaId > 0)
                {
                    sqlwhere += " vi_elevatorinfo.areaId=@areaId AND";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4); ;
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likeno = "%" + elevatorinfo.elevatorNO + "%";
                    string likename = "%" + elevatorinfo.elevatorName + "%";
                    string likearea = "%" + elevatorinfo.areaName + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorNO", $"%{likeno}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@areaName", $"%{likearea}%");
                    mySqlCommand.Parameters.AddWithValue("@areaId", elevatorinfo.areaId);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch (Exception ex)
            {

            }
            return count;
        }

        public List<Elevator> QueryElevatorByArea(Elevator elevatorinfo)
        {
            try
            {
                List<Elevator> list = new List<Elevator>();
                string strSql = "SELECT vi_elevatorinfo.*,area.areaName AS mainAreaName FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id";
                string sqlwhere = string.Empty;
                if (elevatorinfo.areaId > 0)
                {
                    sqlwhere += " vi_elevatorinfo.mainAreaId=@areaId AND";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4); ;
                }
                strSql += " ORDER BY vi_elevatorinfo.id asc";

                if (elevatorinfo.Recordhead > -1 && elevatorinfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@areaId", elevatorinfo.areaId);
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", elevatorinfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", elevatorinfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<Elevator>(dataTable);
                }

                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public int QueryElevatorByAreaConut(Elevator elevatorinfo)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id";
                string sqlwhere = string.Empty;
                if (elevatorinfo.areaId > 0)
                {
                    sqlwhere += " vi_elevatorinfo.mainAreaId=@areaId AND";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4); ;
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@areaId", elevatorinfo.areaId);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch (Exception ex)
            {

            }
            return count;
        }

        public Elevator LoadElevator(int elevatorid)
        {
            string strSql = string.Format("SELECT vi_elevatorinfo.*,area.areaName AS mainAreaName FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id WHERE vi_elevatorinfo.id={0}", elevatorid);
            return SqlHelper<Elevator>.SqlSingleData(strSql);
        }

        public string CleanUpElevator()
        {
            string strSql = string.Format("delete FROM elevator");
            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string CleanUpElevatorPosition()
        {
            string strSql = string.Format("UPDATE elevator_position SET isDelete =1");

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string AddArea(AreaInfo areainfo)
        {
            string strSql = string.Format("INSERT INTO area(areaName,areaMapUrl,creationDate,creationPerson,isDelete,isAll,color,mainAreaId,id)VALUES('{0}','{1}','{2}','{3}',0,{4},'{5}',{6},{7})", areainfo.areaName, areainfo.areaMapUrl, DateTime.Now.ToString(), areainfo.creationPerson, areainfo.isAll, areainfo.color, areainfo.mainAreaId, areainfo.id);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string EditArea(AreaInfo areainfo)
        {
            string strSql = string.Format("UPDATE area SET areaName='{0}',areaMapUrl='{1}',isAll={3},color='{4}',mainAreaId={5} WHERE id={2}", areainfo.areaName, areainfo.areaMapUrl, areainfo.id, areainfo.isAll, areainfo.color, areainfo.mainAreaId);
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    if (SqlHelper<AreaInfo>.SqlExecute(strSql))
                    {
                        return "success";
                    }
                    else
                    {
                        return "fail";
                    }
                }
                catch (Exception e)
                {
                    return "fail," + e.ToString();
                }
            }
        }

        public string DelArea(int areaid)
        {
            string strSql = string.Format("delete FROM area WHERE id={0}", areaid);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string BatchDelArea(string ids)
        {
            string strSql = string.Format("delete FROM area WHERE id in ({0})", ids);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public bool ChekExist(int areaid)
        {
            string strSql = "SELECT COUNT(1) FROM elevator_position WHERE isDelete=0 AND areaId=" + areaid;
            try
            {
                int areacount = SqlHelper<Elevator>.SqlScalar(strSql);

                if (areacount > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                return true;
            }
            return false;

        }

        public List<AreaInfo> QueryArea(AreaInfo areainfo)
        {
            string strSql = "SELECT * FROM area";
            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(areainfo.areaName))
            {
                sqlwhere += " areaName like '%" + areainfo.areaName + "%' AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (areainfo.Recordhead > -1 && areainfo.Recordtail > 0)
            {
                strSql += " LIMIT " + areainfo.Recordhead + "," + areainfo.Recordtail;
            }

            return SqlHelper<AreaInfo>.SqlList(strSql);
        }

        public int QueryAreaCount(AreaInfo areainfo)
        {
            string strSql = "SELECT COUNT(1) FROM area";
            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(areainfo.areaName))
            {
                sqlwhere += " areaName like '%" + areainfo.areaName + "%' AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            int count = 0;
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    count = conn.ExecuteScalar<int>(strSql);
                }
                catch (Exception e)
                {
                    return count;
                }
            }
            return count;
        }

        public string AddElevatorPosition(ElevatorPosition positioninfo)
        {
            string strSql = string.Format("INSERT INTO elevatorposition (areaId,elevatorId,optionX,optionY,creationDate,creationPerson,isDelete)VALUES({0},{1},{2},{3},'{4}','{5}',0)"
                , positioninfo.areaId, positioninfo.elevatorId, positioninfo.optionX, positioninfo.optionY, DateTime.Now.ToString(), positioninfo.creationPerson);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string EditElevatorPosition(ElevatorPosition positioninfo)
        {
            string strSql = string.Format("UPDATE elevator_position SET areaId={0},elevatorId={1},optionX={2},optionY={3} WHERE id={4}", positioninfo.areaId, positioninfo.elevatorId, positioninfo.optionX, positioninfo.optionY, positioninfo.id);

            try
            {
                if (SqlHelper<AreaInfo>.SqlExecute(strSql))
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string DelPosition(int id)
        {
            string strSql = string.Format("UPDATE elevator_position SET isDelete =1 WHERE id={0}", id);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<AreaInfo> ShowAllArea(int isall)
        {
            string strSql = "SELECT * FROM area where isDelete=0";
            if (isall == 0)
            {
                strSql += " AND isAll=0";
            }
            if (isall == 1)
            {
                strSql += " AND isAll=1";
            }

            return SqlHelper<AreaInfo>.SqlList(strSql);
        }

        public Elevator QueryElevatorInfoByOpc(string opcno)
        {
            string strSql = "SELECT vi_elevatorinfo.*,area.areaName AS mainAreaName FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id WHERE vi_elevatorinfo.opcNO='" + opcno + "'";

            return SqlHelper<Elevator>.SqlSingleData(strSql);
        }

        public int QueryRunTime(int elevatorid, int year, int month, int day)
        {
            string strSql = string.Format("SELECT runTime from elevator_runstimes WHERE YEAR(runDate)='{0}' AND MONTH(runDate)='{1}' AND DAY(runDate)='{2}' AND elevatorId={3}", year, month, day, elevatorid);

            return SqlHelper<Elevator>.SqlScalar(strSql);
        }

        public string InsertRunTime(int elevatorid, int runtime, string rundate)
        {
            string strSql = string.Format("insert INTO elevator_runstimes(elevatorId,runDate,runTime)VALUES({0},'{1}',{2})", elevatorid, rundate, runtime);
            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string UpdateRunTime(int elevatorid, int runtime, string rundate)
        {
            string strSql = string.Format("update elevator_runstimes set runTime=runTime+{1} WHERE elevatorId={0} AND runDate='{2}'", elevatorid, runtime, rundate);

            try
            {
                int count = SqlHelper<Elevator>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public int QueryRuns(int elevatorid, int year, int month, int day)
        {
            string strSql = string.Format("SELECT runs from elevator_runs WHERE YEAR(runsDate)='{0}' AND MONTH(runsDate)='{1}' AND DAY(runsDate)='{2}' AND elevatorId={3}", year, month, day, elevatorid);

            return SqlHelper<Elevator>.SqlScalar(strSql);
        }

        public string InsertRuns(int elevatorid, int runtime, string rundate)
        {
            string strSql = string.Format("insert INTO elevator_runs(elevatorId,runsDate,runs)VALUES({0},'{1}',{2})", elevatorid, rundate, runtime);
            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string UpdateRuns(int elevatorid, int runtime, string rundate)
        {
            string strSql = string.Format("update elevator_runs set runs=runs+{1} WHERE elevatorId={0} AND runsDate='{2}'", elevatorid, runtime, Convert.ToDateTime(rundate).ToString("yyyy-MM-dd HH:mm:ss"));

            try
            {
                int count = SqlHelper<Elevator>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<StateStatisticsNum> GetStateInfo()
        {
            string strSql = "SELECT id AS elevator_moving_status,elevatorStateName FROM elevator_state_name WHERE id>0";

            return SqlHelper<StateStatisticsNum>.SqlList(strSql);
        }

        public List<ElevatorPosition> QueryOptionXY(int areaid, int isall)
        {
            try
            {
                List<ElevatorPosition> list = new List<ElevatorPosition>();
                string strSql = "SELECT id,elevatorName,elevatorNo,optionX,optionY,outofservice FROM vi_elevatoroption ";
                if (isall == 0)
                {
                    strSql += " WHERE areaId=@areaId";
                }
                else
                {
                    strSql += " WHERE isAll=1";
                }
                strSql += " AND isDelete=0 AND outofservice=0";
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@areaId", areaid);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<ElevatorPosition>(dataTable);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public List<AreaElevatorCount> QueryElevatorCount()
        {
            string strSql = @"SELECT
	                                    a.id,
	                                    a.areaName,
	                                    a.isAll,
	                                    a.areaMapUrl,
	                                    (
		                                    CASE
		                                    WHEN a.isAll = 0 THEN
			                                    (
				                                    SELECT
					                                    COUNT(1)
				                                    FROM
					                                    elevator_position b
				                                    LEFT JOIN elevator d ON b.elevatorId = d.Id
				                                    WHERE
					                                    areaId = a.id
				                                    AND b.isDelete = 0
				                                    AND d.outofservice = 0
			                                    )
		                                    ELSE
			                                    (
				                                    SELECT
					                                    COUNT(1)
				                                    FROM
					                                    elevator c
				                                    WHERE
					                                    c.isDelete = 0
				                                    AND outofservice = 0
			                                    )
		                                    END
	                                    ) AS ElevatorCount
                                    FROM
	                                    area a
                                    WHERE
	                                    a.isDelete = 0
                                    ORDER BY
	                                    a.isAll DESC";
            return SqlHelper<AreaElevatorCount>.SqlList(strSql);
        }

        public Elevator QueryElevatorByOpc(string opcno)
        {
            string strSql = "SELECT vi_elevatorinfo.*,area.areaName AS mainAreaName FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id WHERE vi_elevatorinfo.opcNO='" + opcno + "'";

            return SqlHelper<Elevator>.SqlSingleData(strSql);
        }

        public string Clearbend(int id)
        {
            string strSql = string.Format("UPDATE elevator SET bend=0,warningtime=NULL WHERE id={0}", id);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public int QueryElevatorFault(int elevatorid)
        {
            try
            {
                string strSql = "SELECT COUNT(1) FROM fault_report WHERE elevatorID=" + elevatorid + " AND isDelete=0";
                return SqlHelper<Elevator>.SqlScalar(strSql);
            }
            catch
            {
                return 0;
            }
        }
        public int QueryElevatorRuns(int elevatorid)
        {
            try
            {
                string strSql = "SELECT SUM(runs) FROM elevator_runs WHERE elevatorId=" + elevatorid;
                return SqlHelper<Elevator>.SqlScalar(strSql);
            }
            catch
            {
                return 0;
            }
        }
        public int QueryElevatorRunTime(int elevatorid)
        {
            try
            {
                string strSql = "SELECT SUM(runTime) FROM elevator_runstimes WHERE elevatorId=" + elevatorid;
                return SqlHelper<Elevator>.SqlScalar(strSql);
            }
            catch
            {
                return 0;
            }
        }

        public string QueryNextMaintenance(int elevatorid)
        {
            try
            {
                string strSql = "select maintenanceDatetime FROM routine_maintain WHERE maintenanceProperties='年度保养' AND isDelete=0 AND elevatorID=" + elevatorid + " AND maintenanceDatetime>'" + DateTime.Now.ToString("yyyy-MM-dd") + "' ORDER BY maintenanceDatetime ASC LIMIT 0,1";
                return SqlHelper<Elevator>.SqlScalarString(strSql);
            }
            catch
            {
                return "";
            }
        }

        public string QuerylastMaintenance(int elevatorid)
        {
            try
            {
                string strSql = "select maintenanceDatetime FROM routine_maintain WHERE maintenanceProperties='年度保养' AND isDelete=0 AND elevatorID=" + elevatorid + " AND maintenanceDatetime<='" + DateTime.Now.ToString("yyyy-MM-dd") + "' ORDER BY maintenanceDatetime DESC LIMIT 0,1";
                return SqlHelper<Elevator>.SqlScalarString(strSql);
            }
            catch
            {
                return "";
            }
        }

        public string QuerylastRepairTime(int elevatorid)
        {
            try
            {
                string strSql = "select maintenanceTime FROM repair_workorder WHERE isDelete=0 AND elevatorID=" + elevatorid + " ORDER BY maintenanceTime DESC LIMIT 0,1";
                return SqlHelper<Elevator>.SqlScalarString(strSql);
            }
            catch
            {
                return "";
            }
        }

        public List<ElevatorType> QueryElevatorType()
        {
            string strSql = "select * FROM elevator_type";
            return SqlHelper<ElevatorType>.SqlList(strSql);
        }

        public string TruncateArea()
        {
            string strSql = "TRUNCATE area";

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public bool CheckArea(int id)
        {
            string strSql = "SELECT COUNT(1) FROM area WHERE id=" + id;
            try
            {
                int areacount = SqlHelper<AreaInfo>.SqlScalar(strSql);

                if (areacount > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                return true;
            }
            return false;
        }

        public int GetFaultListSum(string elevatorids, string endtime, string starttime)
        {
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(starttime))
            {
                sqlwhere += " DATE(faultTime)>=DATE('" + starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sqlwhere += " DATE(faultTime)<=DATE('" + endtime + "') AND";
            }
            if (!string.IsNullOrEmpty(elevatorids))
            {
                sqlwhere += " elevatorId IN(" + elevatorids + ") AND";
            }
            if (sqlwhere.Length > 0)
            {
                sqlwhere = " WHERE" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            string strSql = string.Format(@"select COUNT(1) FROM vi_fault{0}", sqlwhere);

            return SqlHelper<Statistics>.SqlScalar(strSql);
        }

        public List<T> ToList<T>(DataTable dt) where T : class, new()
        {
            List<T> list = new List<T>();
            //确认参数有效,若无效则返回Null
            if (dt == null)
                return list;
            else if (dt.Rows.Count == 0)
                return list;
            Dictionary<string, string> dicField = new Dictionary<string, string>();
            Dictionary<string, string> dicProperty = new Dictionary<string, string>();
            Type type = typeof(T);

            //创建字段字典，方便查找字段名
            foreach (var item in type.GetFields())
            {
                dicField.Add(item.Name.ToLower(), item.Name);
            }
            //创建属性字典，方便查找属性名
            foreach (var item in type.GetProperties())
            {
                dicProperty.Add(item.Name.ToLower(), item.Name);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string memberKey = dt.Columns[j].ColumnName.ToLower();

                    //字段赋值
                    if (dicField.ContainsKey(memberKey))
                    {
                        FieldInfo theField = type.GetField(dicField[memberKey]);
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theField.FieldType;
                            //dbValue = dbValue.ChangeType(memberType);
                            dbValue = Convert.ChangeType(dbValue, memberType);
                        }
                        theField.SetValue(_t, dbValue);
                    }
                    //属性赋值
                    if (dicProperty.ContainsKey(memberKey))
                    {
                        PropertyInfo theProperty = type.GetProperty(dicProperty[memberKey]);
                        if (!theProperty.CanWrite) continue;
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theProperty.PropertyType;
                            if (memberType.IsGenericType && memberType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                            {
                                NullableConverter newNullableConverter = new NullableConverter(memberType);
                                dbValue = newNullableConverter.ConvertFrom(dbValue);
                            }
                            else
                            {
                                dbValue = Convert.ChangeType(dbValue, memberType);
                            }
                        }
                        theProperty.SetValue(_t, dbValue, null);
                    }
                }
                list.Add(_t);
            }
            return list;
        }

        public List<ElevatorRunInfo> QueryElevatorRunTimeTotal()
        {
            try
            {
                string strSql = "SELECT SUM(runTime) AS runTimeTotal,elevatorId FROM elevator_runstimes GROUP BY elevatorId";

                return SqlHelper<ElevatorRunInfo>.SqlList(strSql);
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public List<ElevatorRunInfo> QueryElevatorRunsTotal()
        {
            try
            {
                string strSql = "SELECT SUM(runs) AS runsTotal,elevatorId FROM elevator_runs GROUP BY elevatorId";

                return SqlHelper<ElevatorRunInfo>.SqlList(strSql);
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public List<ElevatorStateRunTime> QueryRunTimeToday(string dt)
        {
            try
            {
                string strSql = "SELECT * FROM elevator_runstimes WHERE DATE(runDate)=DATE('" + dt + "')";

                return SqlHelper<ElevatorStateRunTime>.SqlList(strSql);
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public List<ElevatorStateRun> QueryRunsToday(string dt)
        {
            try
            {
                string strSql = "SELECT * FROM elevator_runs WHERE DATE(runsDate)=DATE('" + dt + "')";

                return SqlHelper<ElevatorStateRun>.SqlList(strSql);
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public string ClearRunSum(int id)
        {
            string strSql = string.Format("UPDATE elevator SET runssum=0 WHERE id={0}", id);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string UpdateRunSum(int id, int runssum)
        {
            string strSql = string.Format("UPDATE elevator SET runssum={1} WHERE id={0}", id, runssum);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public bool UpdateSpace()
        {
            string strSql = "update elevator set elevatorNo=REPLACE(REPLACE(elevatorNo, CHAR(13), ''), CHAR(10), '')";
            try
            {
                int count = 0;
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);
                    connection.Open();
                    count = mySqlCommand.ExecuteNonQuery();
                }

                if (count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public bool UpdatePoint(string elevatorno, double ZLX, double ZLY)
        {
            string strSql = "UPDATE elevator SET ZLX=@ZLX,ZLY=@ZLY WHERE elevatorNo=@elevatorNo";
            //string strSql = "UPDATE elevator SET ZLX=" + ZLX + ",ZLY=" + ZLY + " WHERE elevatorNo='" + elevatorno + "'";
            try
            {
                int count = 0;
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);
                    mySqlCommand.Parameters.AddWithValue("@elevatorNo", elevatorno);
                    mySqlCommand.Parameters.AddWithValue("@ZLX", ZLX);
                    mySqlCommand.Parameters.AddWithValue("@ZLY", ZLY);

                    connection.Open();
                    count = mySqlCommand.ExecuteNonQuery();
                }

                if (count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public string UpdateOpenCount(string opcno, int runtimesum, int bend, int runssum)
        {
            string strSql = "update elevator set runtimesum=@runtimesum,bend=@bend,runssum=@runssum WHERE opcNO=@opcNO";

            try
            {
                int count = 0;
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);
                    mySqlCommand.Parameters.AddWithValue("@runtimesum", runtimesum);
                    mySqlCommand.Parameters.AddWithValue("@runssum", runssum);
                    mySqlCommand.Parameters.AddWithValue("@bend", bend);
                    mySqlCommand.Parameters.AddWithValue("@opcNO", opcno);

                    connection.Open();
                    count = mySqlCommand.ExecuteNonQuery();
                }

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<PanoramicElevator> QueryPanoramicElevator(int Recordhead, int Recordtail)
        {
            try
            {
                List<PanoramicElevator> list = new List<PanoramicElevator>();
                string strSql = "SELECT vi_elevatorinfo.*,area.areaName AS mainAreaName FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id";
                string sqlwhere = string.Empty;

                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4); ;
                }
                strSql += " ORDER BY vi_elevatorinfo.id asc";

                if (Recordhead > -1 && Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<PanoramicElevator>(dataTable);
                }

                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public List<PanoramicElevator> QueryPanoramicElevator(string elevatorno)
        {
            try
            {
                List<PanoramicElevator> list = new List<PanoramicElevator>();
                string strSql = "SELECT vi_elevatorinfo.*,area.areaName AS mainAreaName FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id where vi_elevatorinfo.elevatorNo=@elevatorNo";
                string sqlwhere = string.Empty;

                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4); ;
                }
                strSql += " ORDER BY vi_elevatorinfo.id asc";

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@elevatorNo", elevatorno);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<PanoramicElevator>(dataTable);
                }

                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public int QueryPanoramicElevatorConut()
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id";

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch (Exception ex)
            {

            }
            return count;
        }

        public int QueryPanoramicElevatorConut(string elevatorno)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id where vi_elevatorinfo.elevatorNo=@elevatorNo";

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@elevatorNo", elevatorno);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch (Exception ex)
            {

            }
            return count;
        }

        public Elevator LoadElevatorByNo(string elevatorno)
        {
            string strSql = string.Format("SELECT vi_elevatorinfo.*,area.areaName AS mainAreaName FROM vi_elevatorinfo LEFT JOIN area ON vi_elevatorinfo.mainAreaId=area.id WHERE vi_elevatorinfo.elevatorNo='{0}'", elevatorno);
            return SqlHelper<Elevator>.SqlSingleData(strSql);
        }
    }
}
